   /*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DataBase;

import Data.Car;
import Data.Employee;
import Server.ClientThread;
import XMLParser.MyXMLWriter;
import XMLParserS.Helper;
import java.lang.String;

import java.sql.*;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.logging.Level;
import java.util.logging.Logger;

 /*
  * @author Cristina
  */
public class Requests implements RequestI //implements RequestI
{
   //AnswerInterface ans = null;
   // DataBaseq query = null;
    private MyXMLWriter writer = new MyXMLWriter("localTemp.xml");
   
    private static Connection con = null;
    private Statement statement;
   

    public Requests(String db, String pass)throws Exception
    {
        ConnectDb(db,pass);

    }
    private void ConnectDb(String db, String pass)throws Exception
    {
        pass = "";
        //System.out.println("password_db:***"+pass+"***");
     //   try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            if (con != null)
                con.close();
           // else{
                con = DriverManager.getConnection("jdbc:mysql:///"+db, "root", pass);
                if (!con.isClosed()) {
                    statement = con.createStatement();
                    String showAllTables = "show tables;";
                    statement.executeQuery(showAllTables);
                    ResultSet rs = statement.getResultSet();

                    while (rs.next()) {
                        System.out.println(rs.getString(1));
                    }
                    System.out.println("Successfully connected to MySQL server using TCP/IP...");
                }
            //}// else {
               // System.out.println("Already connected : use the existing connection");
           // }
      //  } catch (Exception e) {
         //   System.err.println("Exception: " + e.getMessage());
       // }
    }


   

    public void CheckUserPassword(ClientThread cl,String db, String user,String pass)
    {
        try {
            System.out.println("databases:"+db+"***");
            String str = null;
            
            String query = "SELECT user, password, type FROM "+db+".login WHERE user= '"+user
                    +"'  AND password= '"+pass+"'";
            ResultSet r = statement.executeQuery(query);
            if (r != null && r.next()) {
                str = r.getString("type");
            } else {
                str = "none";
            }
            String[] tags = {"answer", "check_user", "result"};
            writer.SetTags(Arrays.asList(tags));
            writer.Start();
            String[] data = {"", str};
            writer.Add(Arrays.asList(data));
            writer.Stop();
            cl.SendAnswer("localTemp.xml");
        } catch (SQLException ex) {
            Logger.getLogger(Requests.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

      public void deleteReservation(ClientThread cl, Helper h)
    {    //String db, String fname, String lname, Date dataRezervarii
        try {
            System.out.println("***SUNT IN DELTE RESERVATION");
            String str = null;
            int serie=-1,ruta=-1,ind=0;
            String query = "SELECT ticketSeries, routeId FROM dmstc.ticket WHERE userId="
                    + "(SELECT userId FROM  dmstc.user WHERE lastName='"+h.getLastName()+"' AND firstName='"
                    +h.getFirstName()+"' AND departureDate='"+h.getDataRezervarii()+"')";

            System.out.println("lname:"+h.getLastName()+"   fname="+h.getFirstName()+"     depdate="+h.getDataRezervarii());

            ResultSet r = statement.executeQuery(query);
            if(r!=null && r.next()) {
                System.out.println("***SUNT IN DELTE RESERVATION am gasit ce vreau sa sterg");

                serie = r.getInt("ticketSeries");
                ruta = r.getInt("routeId");
                System.out.println("***seria biletului cautat: "+serie+"si ruta "+ruta);
                ind = 1;
            }
            //Aici daca nu gaseste rezervarea cu numele ala... 
            // trebuie scris un xml si trimis

            if(ind==0){//daca e 0, nu a gasit rezervarea
                System.out.println("***SUNT IN DELTE RESERVATION nu am gasit ce vreau sa sterg");
                str="Nu exista aceasta rezervare";
                    String[] tags = {"answer", "delete_reservation", "error"};
                    writer.SetTags(Arrays.asList(tags));
                    writer.Start();
                    String[] data = {"", str};
                    writer.Add(Arrays.asList(data));
                    writer.Stop();
                    cl.SendAnswer("localTemp.xml");

            }
 else{  System.out.println("***SUNT IN DELTE RESERVATION si urmeaza sa sterg");
            query = "DELETE FROM dmstc.ticket WHERE ticketSeries="+Integer.toString(serie);
           int r1 = statement.executeUpdate(query);

            query = "UPDATE dmstc.route SET reservedSeats=reservedSeats-1 WHERE routeId="+Integer.toString(ruta);
            r1 = statement.executeUpdate(query);
            //si aici iar trebuie completat un xml si trimis
            str="Am sters rezervarea";
            String[] tags = {"answer", "delete_reservation", "result"};
            writer.SetTags(Arrays.asList(tags));
            writer.Start();
            String[] data = {"", str};
            writer.Add(Arrays.asList(data));
            writer.Stop();
            cl.SendAnswer("localTemp.xml");
            }
        } catch (SQLException ex) {
            Logger.getLogger(Requests.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

   public void insertEmployee(ClientThread cl, Helper h)
      {
       String str = null;
       boolean er = false;
        try {
            String query = "INSERT INTO " + h.getDb() + ".employee "
                    + "(`employeeId`, `firstName`, `lastName`, `earning`, `bonus`, `workHours`)"
                    + "VALUES (NULL, '"+h.getFirstName()+"', '"+h.getLastName()+"', '"+h.getEarning()+"','"+
                    +h.getBonus()+"', '"+h.getWorkHours()+"')";
            int r = statement.executeUpdate(query);
            System.out.println("work hours"+h.getWorkHours());
            //trimit un xml la client dc s-a facut inserarea sau nu
            //asa scriu eu xml dc te deranjeaza poti folosi clase gata facute dar mie
            // mi s-a parut mai complicat si mi-am facut eu o clasa nu e prea buna
            // da macar stiu ce face;)
            str = "Successful!";
            
        } catch (SQLException ex) {
            Logger.getLogger(Requests.class.getName()).log(Level.SEVERE, null, ex);
            str = "Inserarea nu a putut avea loc!";
            er = true;
        }finally
        {
           
            if (er)
            {
                String[] tags = {"answer", "insert_employee", "error"};
                writer.SetTags(Arrays.asList(tags));
            }else
            { 
                 String[] tags = {"answer", "insert_employee", "result"};
                 writer.SetTags(Arrays.asList(tags));
            }
           
            writer.Start(); //apelez fc de start
            String[] data = {"", str}; // vectorul ce se afla intre tag anerioare fara radacina  ex
            // <result>str </result>
            writer.Add(Arrays.asList(data));
            writer.Stop();
            cl.SendAnswer("localTemp.xml");// aici le trimit prin firul de executie de pe care am primit cererea
        }


      }

   public void deleteEmployee(ClientThread cl, String fname, String lname){
       try {
           System.out.println("delete "+fname+lname);
           /* String query = "DELETE FROM dmstc.employee "
                    + "WHERE employeeID=(SELECT employeeId FROM dmstc.employee "
                    + "WHERE firstName='" + fname +
                     "' AND lastName='" + lname+"'";*/
           String query = "DELETE FROM dmstc.employee "
                    + "WHERE firstName='" + fname +
                     "' AND lastName='" + lname+"'";
          if( 1 == statement.executeUpdate(query)){
            String str = "Am sters angajatul";
            System.out.println("********"+str);
            String[] tags = {"answer", "delete_employee", "result"};
            writer.SetTags(Arrays.asList(tags)); // primaoara sunt tag in ordinea in care apar de la radacina
            writer.Start(); //apelez fc de start
            String[] data = {"", str}; // vectorul ce se afla intre tag anerioare fara radacina  ex
            // <result>str </result>
            writer.Add(Arrays.asList(data));
            writer.Stop();
            cl.SendAnswer("localTemp.xml");
           }
 else {
                String str = "NU am putut sterge";
            System.out.println("********"+str);
                String[] tags = {"answer", "delete_employee", "error"};
            writer.SetTags(Arrays.asList(tags)); // primaoara sunt tag in ordinea in care apar de la radacina
            writer.Start(); //apelez fc de start
            String[] data = {"", str}; // vectorul ce se afla intre tag anerioare fara radacina  ex
            // <result>str </result>
            writer.Add(Arrays.asList(data));
            writer.Stop();
            cl.SendAnswer("localTemp.xml");

 }

        } catch (SQLException ex) {
            Logger.getLogger(Requests.class.getName()).log(Level.SEVERE, null, ex);
//            String str = "NU am putut sterge";
//            String[] tags = {"answer", "delete_employee", "error"};
//            writer.SetTags(Arrays.asList(tags)); // primaoara sunt tag in ordinea in care apar de la radacina
//            writer.Start(); //apelez fc de start
//            String[] data = {"", str}; // vectorul ce se afla intre tag anerioare fara radacina  ex
//            // <result>str </result>
//            writer.Add(Arrays.asList(data));
//            writer.Stop();
//            cl.SendAnswer("localTemp.xml");


        }


   }


       public void makeReservation(ClientThread cl, Helper h)
    {
        try {
           // System.out.println("databases:"+h.getDb()+"***");
            String str = null;
            int user=-1,ruta=-1;
            int ind=1; int r1=0;
            String query = "SELECT userId FROM  dmstc.user WHERE lastName='"+h.getLastName()+"'";


            ResultSet r = statement.executeQuery(query);
            if(r.next()){
                user = r.getInt("userId");
            }
 else user=-1;
            System.out.println("userID: "+user);
          

            query = "SELECT routeId FROM  dmstc.route WHERE date='"+h.getdataRezervarii()+"'";
            r = statement.executeQuery(query);
           // ruta = r.getInt("routeId");
             if(r.next()){
                ruta = r.getInt("routeId");
            } else ruta=-1;
            System.out.println("rutaID: "+ruta);

            if(user==-1){
                //daca nu exista acel user, il inserez
                System.out.println("NU stiu user deci il inserez in baza de date");
                 query = "INSERT INTO dmstc.user VALUES ( NULL,'"+h.getFirstName()+"','"+h.getLastName()+"',0)";
                 r1 = statement.executeUpdate(query);
                 query = "SELECT userId FROM  dmstc.user WHERE lastName='"+h.getLastName()+"'";
                 r = statement.executeQuery(query);
                 if(r.next()){ user = r.getInt("userId");
                 System.out.println("userID dupa ce ar fi trebuit sa-l insereze: "+user);
                 } else {
                     System.out.println("AICI IL FACE PE IND 0 CAND NU AM PUTUT INSERA NOUL CLIENT");
                        ind = 0;
                        String mesaj = "Nu am putut insera noul client";
                        String[] tags = {"answer", "make_reservation", "error"};
                        writer.SetTags(Arrays.asList(tags));
                        writer.Start();
                        String[] data = {"", str};
                        writer.Add(Arrays.asList(data));
                        writer.Stop();
                        cl.SendAnswer("localTemp.xml");
                 }
               

            }

            if(ruta==-1){
                        query = "INSERT INTO dmstc.route VALUES (NULL ,1 ,1 , 0, 0,'"+Date.valueOf(h.getdataRezervarii())+"')";
                        //care e formatul in care e scrisa data?
                        r1 = statement.executeUpdate(query);
                        query = "SELECT routeId FROM  dmstc.route WHERE date='"+h.getdataRezervarii()+"'";
                        r = statement.executeQuery(query);
                        if(r.next()){ ruta = r.getInt("routeId");
                                System.out.println("rutaID dupa inserare: "+ruta);}
                        else {
                             System.out.println("AICI IL FACE PE IND 0 IN RUTA ==-1");
                            ind = 0;
                            String mesaj = "Nu am putut insera noua ruta";
                            String[] tags = {"answer", "make_reservation", "error"};
                            writer.SetTags(Arrays.asList(tags));
                            writer.Start();
                            String[] data = {"", str};
                            writer.Add(Arrays.asList(data));
                            writer.Stop();
                            cl.SendAnswer("localTemp.xml");
                 }
                
            }
            //fac rezervarea (creez un nou bilet)
            if(ind!=0){
              //inainte sa fac rezervarea, verific daca nucumva exista deja o rezervare pe numele ala la data aia

                 query = "SELECT ticketSeries FROM  ticket WHERE departureDate='"+h.getdataRezervarii()+"' AND userID="+user;
                 r = statement.executeQuery(query);
                 if(r.next()){
                     int x = r.getInt("ticketSeries");
                     str="Exista deja un bilet cu seria "+x+" rezervat la data de "+h.getDataRezervarii()+" pe acest nume";
                    String[] tags = {"answer", "make_reservation", "result"};
                    writer.SetTags(Arrays.asList(tags));
                    writer.Start();
                    String[] data = {"", str};
                    writer.Add(Arrays.asList(data));
                    writer.Stop();
                    cl.SendAnswer("localTemp.xml");
                 }
                else{
                query = "INSERT INTO dmstc.ticket VALUES("
                            + user+",NULL,'"+h.getTicket().getDestination()+"'"
                            + ",'"+h.getTicket().getDeparture()
                            +"','"+h.getTicket().getArrivalDate()
                            +"','"+h.getTicket().getDepartureDate()
                            +"',"+h.getTicket().getPrice()
                            +","+h.getTicket().getDiscount()
                            +",'"+h.getTicket().getType()
                            +"',"+ruta
                            +")";
                    r1 = statement.executeUpdate(query);

                    str="Am facut rezervarea";

                    query = "UPDATE dmstc.route SET reservedSeats=reservedSeats+1 WHERE routeId="+Integer.toString(ruta);
                    r1 = statement.executeUpdate(query);
                    String[] tags = {"answer", "make_reservation", "result"};
                    writer.SetTags(Arrays.asList(tags));
                    writer.Start();
                    String[] data = {"", str};
                    writer.Add(Arrays.asList(data));
                    writer.Stop();
                    cl.SendAnswer("localTemp.xml");
                }
            }
            else{
                 System.out.println("AICI IND 0 SI NU AM PUTUT FACE REZERVAREA");
                    str = "Nu am putut face rezervarea";
                    String[] tags = {"answer", "make_reservation", "error"};
                    writer.SetTags(Arrays.asList(tags));
                    writer.Start();
                    String[] data = {"", str};
                    writer.Add(Arrays.asList(data));
                    writer.Stop();
                    cl.SendAnswer("localTemp.xml");

            }
        } catch (SQLException ex) {
            Logger.getLogger(Requests.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
       public void insertCar( ClientThread cl, String db, Car h){
            try {
            String query = "INSERT INTO " + db + ".car "
                    + "VALUES (NULL, '"+h.getNumber()+"', '"+h.getModel()+"', "+h.getSeatsNumber()+","+
                    +h.getKmNumber()+", "+h.getMaintenanceCost()+", "+h.getFuelConsumption()+")";
            int r = statement.executeUpdate(query);

            //trimit un xml la client dc s-a facut inserarea sau nu
            //asa scriu eu xml dc te deranjeaza poti folosi clase gata facute dar mie
            // mi s-a parut mai complicat si mi-am facut eu o clasa nu e prea buna
            // da macar stiu ce face;)
            String str = "Am inserat masina";
            String[] tags = {"answer", "insert_car", "result"};
            writer.SetTags(Arrays.asList(tags)); // primaoara sunt tag in ordinea in care apar de la radacina
            writer.Start(); //apelez fc de start
            String[] data = {"", str}; // vectorul ce se afla intre tag anerioare fara radacina  ex
            // <result>str </result>
            writer.Add(Arrays.asList(data));
            writer.Stop();
            cl.SendAnswer("localTemp.xml");// aici le trimit prin firul de executie de pe care am primit cererea
        } catch (SQLException ex) {
            Logger.getLogger(Requests.class.getName()).log(Level.SEVERE, null, ex);
            String str = "Nu am putut insera masina";
            String[] tags = {"answer", "insert_car", "error"};
            writer.SetTags(Arrays.asList(tags));
            writer.Start();
            String[] data = {"", str};
            writer.Add(Arrays.asList(data));
            writer.Stop();
            cl.SendAnswer("localTemp.xml");
        }
       }
       public void deleteCar(ClientThread cl, String number){
        try {
            //String query = "DELETE FROM dmstc.car WHERE carID=(SELECT carId FROM dmstc.car WHERE number="+number+")";
            String query = "DELETE FROM dmstc.car WHERE number='"+number+"'";
            int r = statement.executeUpdate(query);
            String str = "Am sters masina";
            String[] tags = {"answer", "delete_car", "result"};
            writer.SetTags(Arrays.asList(tags)); // primaoara sunt tag in ordinea in care apar de la radacina
            writer.Start(); //apelez fc de start
            String[] data = {"", str}; // vectorul ce se afla intre tag anerioare fara radacina  ex
            // <result>str </result>
            writer.Add(Arrays.asList(data));
            writer.Stop();
            cl.SendAnswer("localTemp.xml");
        } catch (SQLException ex) {
            Logger.getLogger(Requests.class.getName()).log(Level.SEVERE, null, ex);
            String str = "Nu am putut sterge masina";
            String[] tags = {"answer", "delete_car", "error"};
            writer.SetTags(Arrays.asList(tags));
            writer.Start();
            String[] data = {"", str};
            writer.Add(Arrays.asList(data));
            writer.Stop();
            cl.SendAnswer("localTemp.xml");
        }


       }
       public void selectCar(ClientThread cl, String db, String carNumber)
       {
             try
            {
                String query = "SELECT * from " + db + ".car WHERE number='"+carNumber+"'" ;
                ResultSet r = statement.executeQuery(query);
                String[] tags = {"select_car", "car", "carId","carNumber", "carModel",
                        "seatsNumber", "kmNumber", "maintenanceCost", "fuelConsumption"};
                writer.SetTags(Arrays.asList(tags));
                writer.Start();
                if(r != null){
                if (r.next())
                {

                    String [] data = {"", r.getString("carId"),r.getString("number"), r.getString("model"),
                        r.getString("seatsNumber"), r.getString("kmNumber"),
                        r.getString("maintenanceCost"), r.getString("fuelConsumption")};
                    writer.Add(Arrays.asList(data));
                    System.out.println(r.getString("number")+ r.getString("model")+
                        r.getString("seatsNumber")+ r.getString("kmNumber")+
                        r.getString("maintenanceCost")+ r.getString("fuelConsumption"));
                    }
                }else
                {
                    writer = null;
                    String [] tag = {"answer", "select_car", "result"};
                    writer.SetTags(Arrays.asList(tag));
                    String [] str = {"", "Dont'e exist"};
                    writer.Add(Arrays.asList(str));
                }

                writer.Stop();
                cl.SendAnswer("localTemp.xml");
            }
            catch (SQLException ex) {
                Logger.getLogger(Requests.class.getName()).log(Level.SEVERE, null, ex);
            }
       }
       public void editCar(ClientThread cl, String db, Car c)
       {
        try {
            String query = "UPDATE " + db + ".car SET "
                    + "number = '"+c.getNumber()
                    + "', model = '" + c.getModel()
                    + "', kmNumber = '"+ c.getKmNumber()
                    + "', seatsNumber = '" + c.getSeatsNumber()
                    + "', maintenanceCost = '" + c.getMaintenanceCost()
                    + "', fuelConsumption = '" + c.getFuelConsumption()
                    + "' WHERE carId='" + c.getCarId()+"'";
           System.out.println("car id == "+c.getCarId()+"***********");
            int r = statement.executeUpdate(query);
            //si aici iar trebuie completat un xml si trimis
            String str = "Modificarea a avut loc";
            String[] tags = {"answer", "edit_car", "result"};
            writer.SetTags(Arrays.asList(tags));
            writer.Start();
            String[] data = {"", str};
            writer.Add(Arrays.asList(data));
            writer.Stop();
            cl.SendAnswer("localTemp.xml");
        } catch (SQLException ex) {
            Logger.getLogger(Requests.class.getName()).log(Level.SEVERE, null, ex);
        }
       }


       public void newUser(ClientThread cl, String db, String fname, String lname, int km)
       {
        try {
            String query = "INSERT INTO " + db + ".user " + "VALUES (NULL, '" + fname + "', '" + lname + "', '" + km + "')";
            int r = statement.executeUpdate(query);

             String[] tags = {"answer", "new_user", "result"};
            writer.SetTags(Arrays.asList(tags));
            writer.Start();

            String[] data = {"", "Succesful!"};
            writer.Add(Arrays.asList(data));
            writer.Stop();
            cl.SendAnswer("localTemp.xml");

        } catch (SQLException ex) {
            Logger.getLogger(Requests.class.getName()).log(Level.SEVERE, null, ex);
        }
       }
       public void deleteUser(ClientThread cl, String db, String fname, String lname){
           String str=null;
            try {
            String query = "DELETE FROM "+db+".user WHERE firstName='"+fname+"' AND lastName='"+lname+"'";
            int r = statement.executeUpdate(query);
            
            str = "Am sters clientul";
            String[] tags = {"answer", "delete_user", "result"};
            writer.SetTags(Arrays.asList(tags)); // primaoara sunt tag in ordinea in care apar de la radacina
            writer.Start(); //apelez fc de start
            String[] data = {"", str}; // vectorul ce se afla intre tag anerioare fara radacina  ex
            // <result>str </result>
            writer.Add(Arrays.asList(data));
            writer.Stop();
            cl.SendAnswer("localTemp.xml");
                
        } catch (SQLException ex) {
            Logger.getLogger(Requests.class.getName()).log(Level.SEVERE, null, ex);
            if(str==null) str = "Nu am putut sterge clientul";
            String[] tags = {"answer", "delete_user", "error"};
            writer.SetTags(Arrays.asList(tags));
            writer.Start();
            String[] data = {"", str};
            writer.Add(Arrays.asList(data));
            writer.Stop();
            cl.SendAnswer("localTemp");
        }
    }

   public void editRoute(ClientThread cl, String driverFName, String driverLName, String carNumber, String date){
        int _driverId=-1, _carId=-1;
        String str=null;
           try {
            String query = "SELECT employeeId from dmstc.employee WHERE lastName='" + driverLName + "' AND firstName='"+driverFName+"'";
            ResultSet r = statement.executeQuery(query);
            if (r.next()) {
                _driverId=r.getInt("employeeId");
            }
            else str="Angajatul nu a fost gasit";
            System.out.println("********************************");
            System.out.println("****In requests in server***********");
            System.out.println(carNumber);
            System.out.println("********************************");
            query="SELECT carId from dmstc.car WHERE number='"+carNumber+"'";
            r = statement.executeQuery(query);
            if (r.next()) {
                _carId=r.getInt("carId");
                System.out.println("*********************** CAR ID "+_carId);
            }
            else if(str==null)str="Masina nu a fost gasita"; else str+=" si Masina nu a fost gasita";

            if(str == null){
                query="UPDATE route SET carId="+Integer.toString(_carId)+", employeeId="+Integer.toString(_driverId)+
                        " WHERE date='"+date+"'";
                int r1 = statement.executeUpdate(query);
                str = "Am facut modificarile";
                String[] tags = {"answer", "edit_route", "result"};
                writer.SetTags(Arrays.asList(tags)); // primaoara sunt tag in ordinea in care apar de la radacina
                writer.Start(); //apelez fc de start
                String[] data = {"", str}; // vectorul ce se afla intre tag anerioare fara radacina  ex
                // <result>str </result>
                writer.Add(Arrays.asList(data));
                writer.Stop();
                cl.SendAnswer("localTemp.xml");

            }
            else{

                String[] tags = {"answer", "edit_route", "error"};
                writer.SetTags(Arrays.asList(tags)); // primaoara sunt tag in ordinea in care apar de la radacina
                writer.Start(); //apelez fc de start
                String[] data = {"", str}; // vectorul ce se afla intre tag anerioare fara radacina  ex
                // <result>str </result>
                writer.Add(Arrays.asList(data));
                writer.Stop();
                cl.SendAnswer("localTemp.xml");

            }
        } catch (SQLException ex) {
            Logger.getLogger(Requests.class.getName()).log(Level.SEVERE, null, ex);
        }


       }
   public void selectAll(ClientThread cl, String db, String table)
       {
      
            try
            {
                if(table.equalsIgnoreCase("car")){
                String query = "SELECT * from " + db + ".car";
                ResultSet r = statement.executeQuery(query);
                String[] tags = {"all_cars", "car", "carId","carNumber", "carModel",
                        "seatsNumber", "kmNumber", "maintenanceCost", "fuelConsumption"};
                writer.SetTags(Arrays.asList(tags));
                writer.Start();
                if(r != null){
                while (r.next())
                {

                    String [] data = {"", r.getString("carId"),r.getString("number"), r.getString("model"),
                        r.getString("seatsNumber"), r.getString("kmNumber"),
                        r.getString("maintenanceCost"), r.getString("fuelConsumption")};
                    writer.Add(Arrays.asList(data));
                    System.out.println(r.getString("number")+ r.getString("model")+
                        r.getString("seatsNumber")+ r.getString("kmNumber")+
                        r.getString("maintenanceCost")+ r.getString("fuelConsumption"));
                    }
                }else
                {
                    //writer = new MyXMLWriter();
                    String [] tag = {"answer", "all_cars", "result"};
                    writer.SetTags(Arrays.asList(tag));
                    String [] str = {"", "No more cars!"};
                    writer.Add(Arrays.asList(str));
                }

                writer.Stop();
                cl.SendAnswer("localTemp.xml");
            }
            if(table.equalsIgnoreCase("user"))
            {
                String query = "SELECT * from " + db + ".user";
                ResultSet r = statement.executeQuery(query);
                String tag[] = {"all_users", "user", "firstName", 
                                    "lastName", "kmNumber"};
                writer.SetTags(Arrays.asList(tag));
                writer.Start();
                while(r.next())
                {
                    String []data = {"",r.getString("firstName"), 
                        r.getString("lastName"), r.getString("kmNumber")};
                    writer.Add(Arrays.asList(data));
                }
                writer.Stop();
                  cl.SendAnswer("localTemp.xml");
            }
           }
            catch (SQLException ex) {
                Logger.getLogger(Requests.class.getName()).log(Level.SEVERE, null, ex);
            }
        
       }

  public void getCitiesList(ClientThread cl, String db)
  {
        try {
            System.out.println("*****************");
            String query = "SELECT departure, arrival FROM " + db + ".cities";
            ResultSet r = statement.executeQuery(query);
            String tag[] = {"cities_list", "city_d", "city_a"};
            writer.SetTags(Arrays.asList(tag));
            writer.Start();
            while ( r.next())
            {
                System.out.println("*******"+r.getString("departure"));
                String []data = { r.getString("departure"), r.getString("arrival")};
                writer.Add(Arrays.asList(data));
                }
             writer.Stop();
               cl.SendAnswer("localTemp.xml");
            }

         catch (SQLException ex) {
            Logger.getLogger(Requests.class.getName()).log(Level.SEVERE, null, ex);
        }
  }
  

  public void getKm(ClientThread cl, String db, String plecare, String sosire)
  {
        try {

//            String query = "SELECT km FROM " + db + ".cities WHERE departure='" + plecare + "' AND arrival = '" + sosire + "'";
//            ResultSet r = statement.executeQuery(query);
            String query = "SELECT km FROM " + db + ".price WHERE departureId ="
                    + "(SELECT citiesId from dmstc.cities WHERE departure='"+plecare+"' ) "+
                    "AND destinationId = (SELECT citiesId from dmstc.cities WHERE departure='"+sosire+"' ) ";
           ResultSet r = statement.executeQuery(query);
            String tag[] = {"answer", "get_km", "km"};
            writer.SetTags(Arrays.asList(tag));
            writer.Start();
            if(r.next()){
              String []str =  {"", r.getString("km")};
             writer.Add(Arrays.asList(str));
            }
            else{
               String [] str = {"", "-1"};
                writer.Add(Arrays.asList(str));
             }
            writer.Stop();
              cl.SendAnswer("localTemp.xml");

        } catch (SQLException ex) {
            Logger.getLogger(Requests.class.getName()).log(Level.SEVERE, null, ex);
        }
  }
  public void editEmployee(ClientThread cl, Employee e){
        try {
             System.out.println("Request editEmployee"+ e.toString());
             String query="Update dmstc.employee SET "
                     + "earning ='"+e.getEarning()+"',"
                     + " workHours = '"+e.getWorkHours()+"',"
                     + " bonus ='"+e.getBonus()+"'"
                     + "WHERE firstName='" + e.getFirstName() +
                     "' AND lastName='" + e.getLastName()+"'";
             int r= statement.executeUpdate(query);
          /*  int id=0;
            String str = null;
            String query = "SELECT employeeId from dmstc.employee WHERE firstName='" + e.getFirstName() + "' AND lastName='" + e.getLastName()+"'";
            ResultSet r = statement.executeQuery(query);
            if(r!=null){
                while(r.next()){
                    id=r.getInt("employeeID");
                }
            }
             else{ str="Nu am gasit acest angajat";}
           if(str!=null){
            query="Update employee SET earning="+e.getEarning()+", workHours="+e.getWorkHours()+", bonus="+e.getBonus();
            int r1= statement.executeUpdate(query);*/
                    
        } catch (SQLException ex) {
            Logger.getLogger(Requests.class.getName()).log(Level.SEVERE, null, ex);
        }


  }


 //*****************************************************************************
 //******************   RAPOARTE ***********************************************
//******************************************************************************

 //Sa returneze cursa dintr-o anumita data, cu toti pasagerii cu numele, cu plecare si destinatie

       public void routeReport(ClientThread cl, String dataCursei){
        try {
            int ruta = -1;
//            String DATE_FORMAT = "yyyy-MM-dd";
//            SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
//            String dataC=sdf.format(dataCursei);
            String query = "SELECT routeId FROM dmstc.route WHERE date='" +dataCursei+"'";
            ResultSet r = statement.executeQuery(query);
            if(r.next()){//daca am un singur rezultat
                       ruta=r.getInt("routeId");


                       query = "SELECT firstName, lastName, departure, destination, ticketSeries, departureDate, price "
                                +" FROM dmstc.user JOIN dmstc.ticket ON user.userId=ticket.userId"
                                +" WHERE ticket.routeId="+ruta;
//
//                       query = "SELECT firstName, lastName, departure, destination,ticketSeries, date, price FROM dmstc.user, dmstc.route, dmstc.ticket"
//                               + "WHERE ticket.routeId=" + ruta + " AND user.userId=ticket.userId";
                       r=statement.executeQuery(query);

                       String[] tags = {"route_report", "ticket", "firstName","lastName","departure",
                       "destination","ticketSeries","date","price"};
                       writer.SetTags(Arrays.asList(tags)); // primaoara sunt tag in ordinea in care apar de la radacina
                       writer.Start(); //apelez fc de start
                       while(r.next()){

                            String[] data = {"", r.getString("firstName"),r.getString("lastName"),
                                r.getString("departure"),r.getString("destination"),
                                r.getString("ticketSeries"),r.getDate("departureDate").toString(),r.getString("price") }; // vectorul ce se afla intre tag anerioare fara radacina  ex
                            // <result>str </result>
                            writer.Add(Arrays.asList(data));

                       }
                       writer.Stop();
                       cl.SendAnswer("localTemp.xml");
            }
            else {
                    String str = "Nu exista o cursa la acea data: "+dataCursei;
                    String[] tags = {"answer", "route_report", "error"};
                    writer.SetTags(Arrays.asList(tags)); // primaoara sunt tag in ordinea in care apar de la radacina
                    writer.Start(); //apelez fc de start
                    String[] data = {"", str}; // vectorul ce se afla intre tag anerioare fara radacina  ex
                    // <result>str </result>
                    writer.Add(Arrays.asList(data));
                    writer.Stop();
                    cl.SendAnswer("localTemp.xml");
            }
           }
             catch (SQLException ex) {
            Logger.getLogger(Requests.class.getName()).log(Level.SEVERE, null, ex);
        }
       }
       public void monthReport(ClientThread cl, String startDate, String endDate){
        try {
            int i=0;
            int rute[] = new int[50];
            //String query = "SELECT routeID from dmstc.route WHERE date BETWEEN '" + startDate + "' AND '" + endDate;
            System.out.println("start date: "+startDate);
            String query = "SELECT routeID from dmstc.route WHERE date BETWEEN '"+startDate+"' AND '"+endDate+"'";
            ResultSet rez = statement.executeQuery(query);
            if(rez!=null){
                while(rez.next()){
                    rute[i] = rez.getInt("routeId"); 
                    System.out.println("RUTA CU ID_UL: "+rute[i]); i++;
                }
            }
           String[] tags = {"month_report", "ticket","firstName","lastName",
                             "departure","destination","ticketSeries","date","price"};
           System.out.println("*********************************************************");
           writer.SetTags(Arrays.asList(tags));
           writer.Start();
            for(int j=0;j<i;j++){
                query="SELECT firstName, lastName, departure, destination, ticketSeries, departureDate, price"
                                    +" FROM dmstc.user JOIN dmstc.ticket ON user.userId=ticket.userId"
                                    +" WHERE ticket.routeId="+rute[j];
                rez = statement.executeQuery(query);
                if(rez!=null){
                    while(rez.next()){
                           // System.out.println("ROW COUNT FOR ROUTE ID="+rute[j]+": "+rez.getInt("rowcount"));
//                            System.out.println(rez.getString("firstName")+" "+rez.getString("lastName")+" "+
//                                rez.getString("departure")+" "+rez.getString("destination")+" "+
//                                rez.getString("ticketSeries")+" "+rez.getDate("departureDate").toString()+" "+rez.getString("price") );
                            String[] data = {"", rez.getString("firstName"),rez.getString("lastName"),
                                rez.getString("departure"),rez.getString("destination"),
                                rez.getString("ticketSeries"),rez.getDate("departureDate").toString(),rez.getString("price") };
                            writer.Add(Arrays.asList(data));

                            System.out.println(rez.getString("firstName")+rez.getString("lastName")+
                                rez.getString("departure")+rez.getString("destination")+
                                rez.getString("ticketSeries")+rez.getDate("departureDate").toString()+rez.getString("price"));
                    }
                }

            }
            writer.Stop();
            cl.SendAnswer("localTemp.xml");

        } catch (SQLException ex) {
            Logger.getLogger(Requests.class.getName()).log(Level.SEVERE, null, ex);
        }

           
       }

       public void totalMaintananceCost(ClientThread cl){
        try {
            int costTotal = 0, ind = 1;
  
            String query = "SELECT SUM(maintenanceCost) as total FROM dmstc.car";
            ResultSet rez = statement.executeQuery(query);
            if(rez != null && rez.next()) costTotal = rez.getInt("total");

            String[] tags = {"total_cost", "car","carNumber","maintenanceCost","carModel","totalCost"};
           writer.SetTags(Arrays.asList(tags));
           writer.Start();
            query = "SELECT number, maintenanceCost, model FROM dmstc.car";
            ResultSet r = statement.executeQuery(query);
            if(r!=null){
            while(r.next()){
                 String[] data = {"", r.getString("number"), Integer.toString(r.getInt("maintenanceCost")),
                 r.getString("model"),Integer.toString(costTotal) };
                writer.Add(Arrays.asList(data));

            }
            } else ind = 0;

            writer.Stop();
            cl.SendAnswer("localTemp.xml");
            

        } catch (SQLException ex) {
            Logger.getLogger(Requests.class.getName()).log(Level.SEVERE, null, ex);
        }

       }

        public void employeeSalary(ClientThread cl){
        try {
            int costTotal = 0, ind = 1;


           String[] tags = {"employee_salary", "employee","firstName","lastName","earning","workHours","bonus"};
           writer.SetTags(Arrays.asList(tags));
           writer.Start();
           String query = "SELECT firstName, lastName, earning, workHours,bonus FROM dmstc.employee";
            ResultSet r = statement.executeQuery(query);
            if(r!=null){
            while(r.next()){
                 String[] data = {"", r.getString("firstName"), r.getString("lastName"), Integer.toString(r.getInt("earning")),
                 Integer.toString(r.getInt("workHours")), Integer.toString(r.getInt("bonus")) };
                writer.Add(Arrays.asList(data));

            }
            } else ind = 0;

            writer.Stop();
            cl.SendAnswer("localTemp.xml");


        } catch (SQLException ex) {
            Logger.getLogger(Requests.class.getName()).log(Level.SEVERE, null, ex);
        }

       }


     
}
